"""
数据库服务

提供基础的数据库操作接口
"""

import sqlite3
import aiosqlite
from pathlib import Path
from typing import List, Dict, Any, Optional, Union
from loguru import logger

from src.core.di import Service


@Service("database_service")
class DatabaseService:
    """
    数据库服务
    
    提供异步的SQLite数据库操作接口
    """

    def __init__(self, db_path: str = "data/templates.db"):
        self.db_path = db_path
        self.logger = logger.bind(component="DatabaseService")
        
        # 确保数据目录存在
        Path(db_path).parent.mkdir(parents=True, exist_ok=True)

    async def execute(self, sql: str, params: tuple = ()) -> int:
        """
        执行SQL语句（INSERT, UPDATE, DELETE）
        
        Args:
            sql: SQL语句
            params: 参数
            
        Returns:
            影响的行数
        """
        async with aiosqlite.connect(self.db_path) as db:
            cursor = await db.execute(sql, params)
            await db.commit()
            return cursor.rowcount

    async def fetch_one(self, sql: str, params: tuple = ()) -> Optional[Dict[str, Any]]:
        """
        查询单条记录
        
        Args:
            sql: SQL语句
            params: 参数
            
        Returns:
            查询结果字典，如果没有结果返回None
        """
        async with aiosqlite.connect(self.db_path) as db:
            db.row_factory = aiosqlite.Row
            cursor = await db.execute(sql, params)
            row = await cursor.fetchone()
            return dict(row) if row else None

    async def fetch_all(self, sql: str, params: tuple = ()) -> List[Dict[str, Any]]:
        """
        查询多条记录
        
        Args:
            sql: SQL语句
            params: 参数
            
        Returns:
            查询结果列表
        """
        async with aiosqlite.connect(self.db_path) as db:
            db.row_factory = aiosqlite.Row
            cursor = await db.execute(sql, params)
            rows = await cursor.fetchall()
            return [dict(row) for row in rows]

    async def execute_many(self, sql: str, params_list: List[tuple]) -> int:
        """
        批量执行SQL语句
        
        Args:
            sql: SQL语句
            params_list: 参数列表
            
        Returns:
            总影响的行数
        """
        async with aiosqlite.connect(self.db_path) as db:
            cursor = await db.executemany(sql, params_list)
            await db.commit()
            return cursor.rowcount

    async def execute_script(self, script: str):
        """
        执行SQL脚本
        
        Args:
            script: SQL脚本
        """
        async with aiosqlite.connect(self.db_path) as db:
            await db.executescript(script)
            await db.commit()

    async def get_table_info(self, table_name: str) -> List[Dict[str, Any]]:
        """
        获取表结构信息
        
        Args:
            table_name: 表名
            
        Returns:
            表结构信息列表
        """
        return await self.fetch_all(f"PRAGMA table_info({table_name})")

    async def table_exists(self, table_name: str) -> bool:
        """
        检查表是否存在
        
        Args:
            table_name: 表名
            
        Returns:
            表是否存在
        """
        result = await self.fetch_one(
            "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
            (table_name,)
        )
        return result is not None

    async def get_database_info(self) -> Dict[str, Any]:
        """
        获取数据库信息
        
        Returns:
            数据库信息字典
        """
        # 获取所有表
        tables = await self.fetch_all(
            "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
        )
        
        db_info = {
            "database_path": self.db_path,
            "tables": []
        }
        
        for table in tables:
            table_name = table["name"]
            
            # 获取表的行数
            count_result = await self.fetch_one(f"SELECT COUNT(*) as count FROM {table_name}")
            row_count = count_result["count"] if count_result else 0
            
            # 获取表结构
            columns = await self.get_table_info(table_name)
            
            db_info["tables"].append({
                "name": table_name,
                "row_count": row_count,
                "columns": len(columns),
                "column_info": columns
            })
        
        return db_info

    async def backup_database(self, backup_path: str):
        """
        备份数据库
        
        Args:
            backup_path: 备份文件路径
        """
        import shutil
        
        # 确保备份目录存在
        Path(backup_path).parent.mkdir(parents=True, exist_ok=True)
        
        # 复制数据库文件
        shutil.copy2(self.db_path, backup_path)
        self.logger.info(f"数据库已备份到: {backup_path}")

    async def vacuum(self):
        """
        优化数据库（VACUUM操作）
        """
        async with aiosqlite.connect(self.db_path) as db:
            await db.execute("VACUUM")
            await db.commit()
        self.logger.info("数据库优化完成")

    async def get_database_size(self) -> Dict[str, Union[int, str]]:
        """
        获取数据库大小信息
        
        Returns:
            数据库大小信息
        """
        db_file = Path(self.db_path)
        
        if not db_file.exists():
            return {"size_bytes": 0, "size_mb": 0, "size_readable": "0 B"}
        
        size_bytes = db_file.stat().st_size
        size_mb = size_bytes / (1024 * 1024)
        
        # 可读的大小格式
        if size_bytes < 1024:
            size_readable = f"{size_bytes} B"
        elif size_bytes < 1024 * 1024:
            size_readable = f"{size_bytes / 1024:.1f} KB"
        elif size_bytes < 1024 * 1024 * 1024:
            size_readable = f"{size_mb:.1f} MB"
        else:
            size_readable = f"{size_bytes / (1024 * 1024 * 1024):.1f} GB"
        
        return {
            "size_bytes": size_bytes,
            "size_mb": size_mb,
            "size_readable": size_readable
        }

    async def close(self):
        """
        关闭数据库连接（在这个实现中不需要显式关闭）
        """
        pass

    def __del__(self):
        """析构函数"""
        pass
